﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using JKF.DataModule.IDAL;
using JKF.Substructure;//请先添加引用

namespace JKF.DataModule.SqlServerDAL
{
    public partial class dalPOINT_LOG : IdalPOINT_LOG
    {
        private const string FieldShow = "CUSTOMER_KEY,CARD_NO,[NAME],MOBILE,GIVE_POINTS,CONSUME_POINTS,(GIVE_POINTS+CONSUME_POINTS) as TOTAL_POINT ";
        private const string FieldOrder = "NAME";
        private const string FieldKey = "CUSTOMER_KEY";

        public DataTable PointSearch(string searchItem, string matchWay, string keyword, string minValue, string maxValue,
         string ownShop, string userKey, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder(@"  1=1 and STATUS=1 ");
            sql.AppendFormat(" and exists(select shop_code from sys_user_shop  where CUSTOMER.shop_code=sys_user_shop.shop_code  and sys_user_shop.USER_KEY='{0}')", userKey);

            if (!string.IsNullOrEmpty(ownShop))
            {
                sql.AppendFormat(" and Shop_code in('{0}')", string.Join("','", ownShop.Split(',')));
            }

            if (!string.IsNullOrEmpty(searchItem) && !string.IsNullOrEmpty(keyword))
            {
                if (matchWay == "包含")
                {
                    sql.AppendFormat(" AND {0} LIKE '%{1}%' ", searchItem, keyword);
                }
                else
                {
                    sql.AppendFormat(" AND {0} = '{1}' ", searchItem, keyword);
                }
            }
            if (!string.IsNullOrEmpty(minValue))
            {
               // sql.AppendFormat(" and (GIVE_POINTS+CONSUME_POINTS) >'{0}'", minValue);
                sql.AppendFormat(" and GIVE_POINTS >'{0}'", minValue);
            }
            if (!string.IsNullOrEmpty(maxValue))
            {
                sql.AppendFormat(" and GIVE_POINTS<'{0}'", maxValue);
            }

            DataTable dt= DataPage.GetPageInfo("CUSTOMER", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

            DataColumn column = new DataColumn("TOTAL_TRY_POINT", typeof(System.Int32));
            dt.Columns.Add(column);

            string customerKeys = "";
            foreach (DataRow tmpRow in dt.Rows)
            {
                customerKeys += tmpRow["CUSTOMER_KEY"].ToString() + ",";
            }

            DataTable dt2 = GetTotalTryPointValue(customerKeys);

            for (int i = 0; i < dt.Rows.Count; i++)
			{
                DataRow [] tmpRows= dt2.Select(string.Format("CUSTOMER_KEY='{0}'",dt.Rows[i]["CUSTOMER_KEY"].ToString()));
                if (tmpRows.Length > 0)
                    dt.Rows[i]["TOTAL_TRY_POINT"] = tmpRows[0]["TOTAL_TRY_POINT"];
                else
                    dt.Rows[i]["TOTAL_TRY_POINT"] = 0;
			}
            return dt;
        }

        /// <summary>
        /// 返回顾客体验总积分
        /// </summary>
        /// <param name="customerKeys">顾客key</param>
        /// <returns></returns>
        public DataTable GetTotalTryPointValue(string customerKeys)
        {
            string sql = "";
            sql =@"select CUSTOMER_KEY,COUNT(*) TOTAL_TRY_POINT 
            from dbo.SWING_CARD a
            inner join (select * from Split_Max(@customerKeys,',') where len(value)=36)  b on  a.CUSTOMER_KEY=b.value
            WHERE a.STATUS='1' 
            GROUP BY a.CUSTOMER_KEY ";

             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(sql);
             dbClass.AddInParameter(cmd, "customerKeys", DbType.String, customerKeys);
             var dt = dbClass.ExecuteDataSet(cmd).Tables[0];
             return dt;
        }
    }
}
